﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using GoShopping.Model;
using System.Data;
using GoShopping.Model.Enumerations;
using GoShopping.Model.CustomDefine;

namespace GoShopping.BLL
{
    /// <summary>
    /// 商品类型操作类
    /// </summary>
    public class ProductTypeBean
    {
        /// <summary>
        /// 递归算法中获取不重复表名的次数
        /// </summary>
        private static int _GetUnRepeatNameCount;
        /// <summary>
        /// 获取所有商品类型
        /// </summary>
        /// <returns></returns>
        public IList<ProductType> GetAllProductTypeList()
        {
            IList<ProductType> productTypeList = Databases.DBGoShopping.Select(GoShoppingNBear.ProductType).ToList<ProductType>();
            return productTypeList;
        }

        /// <summary>
        /// 根据条件获取商品类型
        /// </summary>
        /// <returns></returns>
        public IList<ProductType> GetAllProductTypeList(string typeNameCondition)
        {
            string s = string.Format("%{0}%", typeNameCondition);
            IList<ProductType> productTypeList = Databases.DBGoShopping.Select(GoShoppingNBear.ProductType).Where(GoShoppingNBear.ProductType.TypeName.Like(s)).ToList<ProductType>();
            return productTypeList;
        }

        /// <summary>
        /// 查询指定品牌的商品类型
        /// 通过子查询实现
        /// 如果需要模糊匹配，需要该此函数
        /// </summary>
        /// <param name="brandName">Brand</param>
        /// <returns></returns>
        public IList<ProductType> GetBrandProductType(string brandName)
        {
            return Databases.DBGoShopping.Select(GoShoppingNBear.ProductType).Where(GoShoppingNBear.ProductType.ID.In(
                Databases.DBGoShopping.Select(GoShoppingNBear.Product, GoShoppingNBear.Product.ProductTypeID).Where(GoShoppingNBear.Product.Brand == brandName).ToSubQuery()
                )).ToList<ProductType>();
        }

        /// <summary>
        /// 更新一个商品类型
        /// [根据ProductType.ID]
        /// </summary>
        /// <param name="prdctType"></param>
        /// <returns></returns>
        public bool UpdateOneProductType(ProductType prdctType)
        {
            int i = Databases.DBGoShopping.Update(GoShoppingNBear.ProductType).AddColumn(
                GoShoppingNBear.ProductType.ParentID, prdctType.ParentID).AddColumn(
                GoShoppingNBear.ProductType.Remark, prdctType.Remark).AddColumn(
                GoShoppingNBear.ProductType.TypeName, prdctType.TypeName).Where(
                GoShoppingNBear.ProductType.ID == prdctType.ID).Execute();
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 批量更新产品类型
        /// </summary>
        /// <param name="typeList"></param>
        /// <returns></returns>
        public bool UpdateBatchProductType(IList<ProductType> typeList)
        {
            try
            {
                using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required))
                {
                    foreach (ProductType type in typeList)
                    {
                        UpdateOneProductType(type);
                    }
                    scope.Complete();
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 添加一个商品类型
        /// [一级类型没有属性，二级类型可以添加属性]
        /// [如果是一级类型，那么prdctType.ParentID = -1]
        /// [DynamicTableName字段不用添加]
        /// [添加商品类型的同时，添加该类型的详细信息表,而且表名一次生效，终生有效]
        /// </summary>
        /// <param name="prdctType"></param>
        /// <returns></returns>
        public bool AddProductType(ProductType prdctType)
        {
            try
            {
                using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required))
                {
                    string typeTableName;
                    if (prdctType.ParentID == -1)
                    {
                        typeTableName = string.Empty;
                    }
                    else
                    {
                        typeTableName = GetProductTypeDetailTableName(prdctType.TypeName);
                    }
                    int hasTableNameCount = Databases.DBGoShopping.Select(GoShoppingNBear.ProductType, GoShoppingNBear.ProductType.ID.Count()).Where(
                        GoShoppingNBear.ProductType.DynamicTableName == typeTableName).ToScalar<int>();

                    int i = Databases.DBGoShopping.Insert(GoShoppingNBear.ProductType).AddColumn(
                        GoShoppingNBear.ProductType.ParentID, prdctType.ParentID).AddColumn(
                        GoShoppingNBear.ProductType.Remark, prdctType.Remark).AddColumn(
                        GoShoppingNBear.ProductType.DynamicTableName, typeTableName).AddColumn(
                        GoShoppingNBear.ProductType.TypeName, prdctType.TypeName).Execute();
                    if (i < 0)
                    {
                        return false;
                    }
                    if (prdctType.ParentID != -1)
                    {
                        //如果该类型对应的表名已经存在，则不允许添加该商品类型
                        if (hasTableNameCount > 0)
                        {
                            _GetUnRepeatNameCount = 0;
                            typeTableName = GetUnRepeatTableName(typeTableName);
                            //scope.Dispose();
                            //return false;
                        }
                        //如果ParentID不存在,则抛出异常;
                        int parentCount = Databases.DBGoShopping.Select(GoShoppingNBear.ProductType, GoShoppingNBear.ProductType.ID.Count()).Where(
                            GoShoppingNBear.ProductType.ID == prdctType.ParentID).ToScalar<int>();
                        if (parentCount != 1)
                        {
                            throw (new Exception("prdctType:ParentID is not find!"));
                        }
                        //创建该类型对应的详细属性表
                        string sql = "Create Table " + typeTableName + "(ID int not null)";
                        //"(ID int identity constraint PK_" + tableName + "_ID primary key (ID) )";
                        Databases.DBGoShopping.CustomSql(sql).ExecuteNonQuery();
                    }
                    scope.Complete();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个不再重复的表名
        /// </summary>
        /// <param name="typeTableName"></param>
        /// <returns></returns>
        private string GetUnRepeatTableName(string typeTableName)
        {
            _GetUnRepeatNameCount++;
            if (_GetUnRepeatNameCount > 500)
            {
                throw (new Exception("类型名重复,不能录入."));
            }
            System.Random rd = new Random();
            int value = rd.Next(1000);
            string newTableName = typeTableName + value.ToString("#0");
            int hasTableNameCount = Databases.DBGoShopping.Select(GoShoppingNBear.ProductType, GoShoppingNBear.ProductType.ID.Count()).Where(
                    GoShoppingNBear.ProductType.DynamicTableName == newTableName).ToScalar<int>();
            if (hasTableNameCount > 0)
            {
                return GetUnRepeatTableName(typeTableName);
            }
            else
            {
                return newTableName;
            }
        }

        /// <summary>
        /// 获取一个商品类型
        /// </summary>
        /// <param name="productTypeID"></param>
        /// <returns></returns>
        public ProductType GetProductType(int productTypeID)
        {
            ProductType t = Databases.DBGoShopping.Select(GoShoppingNBear.ProductType).Where(
                GoShoppingNBear.ProductType.ID == productTypeID).ToSingleObject<ProductType>();
            return t;
        }

        /// <summary>
        /// 删除一个商品类型
        /// ??是否删除其对应的Goods
        /// </summary>
        /// <param name="productTypeID"></param>
        /// <returns></returns>
        public bool DelProductType(int productTypeID)
        {
            using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required))
            {
                int i;
                ProductType pType = this.GetProductType(productTypeID);
                if (pType == null)
                {
                    return false;
                }
                if (pType.ParentID == -1)
                {//如果有子类型
                    IList<ProductType> leafType = Databases.DBGoShopping.Select(GoShoppingNBear.ProductType).Where(
                        GoShoppingNBear.ProductType.ParentID == pType.ID).ToList<ProductType>();
                    foreach (ProductType p in leafType)
                    {
                        DeleteLeafNode(p.ID);
                    }
                }
                //最后在商品类型表中删除自己
                i = Databases.DBGoShopping.Delete(GoShoppingNBear.ProductType).Where(
                    GoShoppingNBear.ProductType.ID == productTypeID).Execute();
                if (i < 0)
                {
                    return false;
                }
                scope.Complete();
                return true;
            }
        }

        /// <summary>
        /// 删除一个子类型
        /// </summary>
        /// <param name="productTypeID"></param>
        private void DeleteLeafNode(int productTypeID)
        {
            ProductType pType = this.GetProductType(productTypeID);
            if (pType.ParentID == -1)
            {
                throw (new Exception("DeleteLeafNode is Not LeafNode!"));
            }
            //删除该类型对应的商品*****************************
            Databases.DBGoShopping.Delete(GoShoppingNBear.Product).Where(
                GoShoppingNBear.Product.ProductTypeID == productTypeID).Execute();

            //删除动态表
            string sql = "DROP TABLE " + pType.DynamicTableName;
            Databases.DBGoShopping.CustomSql(sql).ExecuteNonQuery();

            //删除该类型的属性
            Databases.DBGoShopping.Delete(GoShoppingNBear.ProductType_RelateColumn).Where(
                GoShoppingNBear.ProductType_RelateColumn.ProductTypeID == productTypeID).Execute();
            //删除自己
            Databases.DBGoShopping.Delete(GoShoppingNBear.ProductType).Where(
                GoShoppingNBear.ProductType.ID == productTypeID).Execute();
        }

        /// <summary>
        /// 删除一个类型的属性
        /// </summary>
        /// <param name="productTypeID"></param>
        /// <param name="propertyChName"></param>
        /// <returns></returns>
        public bool DelProductTypeProperty(int productTypeID, string propertyChName)
        {
            using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required))
            {
                int i;
                ProductType prdctType = this.GetProductType(productTypeID);
                if (prdctType == null)
                {
                    scope.Dispose();
                    return false;
                }
                string tableName = prdctType.DynamicTableName;
                if (prdctType.ParentID != -1)
                {
                    ProductType_RelateColumn column = Databases.DBGoShopping.Select(GoShoppingNBear.ProductType_RelateColumn).Where(
                        GoShoppingNBear.ProductType_RelateColumn.PropertyChName == propertyChName &&
                        GoShoppingNBear.ProductType_RelateColumn.ProductTypeID == productTypeID).ToSingleObject<ProductType_RelateColumn>();
                    string propertyColumnName = column.PropertyColumnName;
                    string sql = string.Format("ALTER TABLE {0} DROP COLUMN {1};", tableName, propertyColumnName);
                    i = Databases.DBGoShopping.CustomSql(sql).ExecuteNonQuery();
                    //if (i < 0)
                    //{
                    //    return false;
                    //}
                }
                else
                {
                    scope.Dispose();
                    throw (new Exception("DelProductTypeProperty Must Be LeafType Property"));
                }

                i = Databases.DBGoShopping.Delete(GoShoppingNBear.ProductType_RelateColumn).Where(
                    GoShoppingNBear.ProductType_RelateColumn.ProductTypeID == productTypeID &&
                    GoShoppingNBear.ProductType_RelateColumn.PropertyChName == propertyChName).Execute();
                if (i < 0)
                {
                    scope.Dispose();
                    return false;
                }
                scope.Complete();
                return true;
            }
        }
        /// <summary>
        /// 给商品类型添加一个属性
        /// </summary>
        /// <param name="property"></param>
        /// <returns></returns>
        public bool AddProductTypeProperty(ProductTypeProperty property)
        {
            using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required))
            {
                string property2ColumnName = this.GetPropertyDetailColumnName(property.PropertyName);
                //一个类型的列名必须唯一
                int hasColumnNameCount = Databases.DBGoShopping.Select(GoShoppingNBear.ProductType_RelateColumn, GoShoppingNBear.ProductType_RelateColumn.PropertyColumnName.Count()).Where(
                    GoShoppingNBear.ProductType_RelateColumn.PropertyColumnName == property2ColumnName &&
                    GoShoppingNBear.ProductType_RelateColumn.ProductTypeID == property.ProdyctTypeID).ToScalar<int>();
                if (hasColumnNameCount > 0)
                {//已经有该列名的属性
                    scope.Dispose();
                    return false;
                }

                int i = Databases.DBGoShopping.Insert(GoShoppingNBear.ProductType_RelateColumn).AddColumn(
                    GoShoppingNBear.ProductType_RelateColumn.ColumnType, property.ValueType.ToString()).AddColumn(
                    GoShoppingNBear.ProductType_RelateColumn.SelectTypeValue, property.SelectTypeValue).AddColumn(
                    GoShoppingNBear.ProductType_RelateColumn.ProductTypeID, property.ProdyctTypeID).AddColumn(
                    GoShoppingNBear.ProductType_RelateColumn.PropertyColumnName,property2ColumnName).AddColumn(
                    GoShoppingNBear.ProductType_RelateColumn.PropertyChName, property.PropertyName).Execute();
                if (i < 0)
                {
                    scope.Dispose();
                    return false;
                }

                ProductType pType = this.GetProductType(property.ProdyctTypeID);
                if (pType == null)
                {
                    scope.Dispose();
                    return false;
                }

                string tableName = pType.DynamicTableName;
                string columnname = GetPropertyDetailColumnName(property.PropertyName);
                string columnType = GetDBValueType(property.ValueType);
                string sql = "Alter Table " + tableName +
                    " Add " + columnname + " " + columnType;
                i = Databases.DBGoShopping.CustomSql(sql).ExecuteNonQuery();
                //if (i < 0)
                //{
                //    return false;
                //}
                scope.Complete();
                return true;
            }
        }

        /// <summary>
        ///  取出该商品类型的属性
        /// </summary>
        /// <param name="productTypeID"></param>
        /// <returns></returns>
        public IList<ProductTypeProperty> GetProductTypeProperties(int productTypeID)
        {
            IList<ProductTypeProperty> propertiesList = new List<ProductTypeProperty>();
            IList<ProductType_RelateColumn> productTypePrptArr = Databases.DBGoShopping.Select(
                GoShoppingNBear.ProductType_RelateColumn).Where(
                GoShoppingNBear.ProductType_RelateColumn.ProductTypeID == productTypeID).ToList<ProductType_RelateColumn>();
            foreach (ProductType_RelateColumn u in productTypePrptArr)
            {
                ProductTypeProperty t = new ProductTypeProperty();
                t.ProdyctTypeID = productTypeID;
                t.PropertyName = u.PropertyChName;
                t.SelectTypeValue = u.SelectTypeValue;
                t.ValueType = (PropertyValueType)Enum.Parse(typeof(PropertyValueType), u.ColumnType);
                propertiesList.Add(t);
            }
            return propertiesList;
        }

        /// <summary>
        ///  取出该商品类型的属性
        ///  [带属性Value字段]
        /// </summary>
        /// <param name="productTypeID"></param>
        /// <returns></returns>
        public IList<ProductPropertyValueList> GetProductTypePrptValue(int productTypeID)
        {
            IList<ProductPropertyValueList> propertiesList = new List<ProductPropertyValueList>();
            IList<ProductType_RelateColumn> productTypePrptArr = Databases.DBGoShopping.Select(
                GoShoppingNBear.ProductType_RelateColumn).Where(
                GoShoppingNBear.ProductType_RelateColumn.ProductTypeID == productTypeID).ToList<ProductType_RelateColumn>();
            foreach (ProductType_RelateColumn u in productTypePrptArr)
            {
                ProductPropertyValueList t = new ProductPropertyValueList();
                t.ProdyctTypeID = productTypeID;
                t.PropertyName = u.PropertyChName;
                t.SelectTypeValue = u.SelectTypeValue;
                t.ValueType = (PropertyValueType)Enum.Parse(typeof(PropertyValueType), u.ColumnType);
                t.PropertyColumnName = u.PropertyColumnName;
                propertiesList.Add(t);
            }
            return propertiesList;
        }

        #region Mapping AutoMatic PropertyTable
        /// <summary>
        /// 根据用户选择的属性类型,返回数据库中的数据类型
        /// 暂只适应 SQL Server
        /// </summary>
        /// <param name="valueType"></param>
        private string GetDBValueType(PropertyValueType valueType)
        {
            switch (valueType)
            {
                case PropertyValueType.浮点:
                    return "decimal(10,2)";
                case PropertyValueType.日期:
                    return "datetime";
                case PropertyValueType.选择:
                    return "varchar(200)";
                case PropertyValueType.整型:
                    return "int";
                case PropertyValueType.字符:
                    return "varchar(500)";
                default:
                    return "varchar(500)";
            }
        }

        /// <summary>
        /// 取出该类型对应的详细属性的表名
        /// TLB_DscrptnPrpt
        /// </summary>
        /// <param name="ProductTypeCHName">类型的中文名</param>
        /// <returns></returns>
        internal string GetProductTypeDetailTableName(string ProductTypeCHName)
        {
            if (ProductTypeCHName == string.Empty)
            {
                return string.Empty;
            }
            GoShopping.Utility.Chs2PY c = new GoShopping.Utility.Chs2PY();
            string s = "TB_PRPT" + c.Convert(ProductTypeCHName);
            return s;
        }
        /// <summary>
        /// 取出该属性对应的字段名
        /// Cbaozhiqi
        /// </summary>
        /// <param name="propertyCHName">属性的中文名 比如：保质期</param>
        /// <returns>baozhiqi</returns>
        internal string GetPropertyDetailColumnName(string propertyCHName)
        {
            GoShopping.Utility.Chs2PY c = new GoShopping.Utility.Chs2PY();
            string s = "C" + c.Convert(propertyCHName);
            return s;
        }

        #endregion
    }
}
